﻿using LFramework.Data;
using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;

namespace DataServices
{
    /*
     * 20140831
     * 陈伟
     * 艺术品数据库操作类
     */
    public class DaoArtWork
    {
        IDBHelper dbhelper = DBFactory.CreateFromConfig("conn");

        /// <summary>
        /// 新增艺术品
        /// </summary>
        /// <param name="artWork">艺术品类</param>
        public void AddArtWork(ArtWorkInfo artWork)
        {
            string sql = @"
insert into tbArtWork(
[ID],
[ArtistID],
[ArtistCategory],
[ArtWorkName],
[Spec],
[PrePrice],
[SaleTime],
[SaleCompany],
[SaleAuct],
[AuctState],
[TransactionPrice],
[Introduction]
)
values
(@ID,
@ArtistID,
@ArtistCategory,
@ArtWorkName,
@Spec,
@PrePrice,
@SaleTime,
@SaleCompany,
@SaleAuct,
@AuctState,
@TransactionPrice,
@Introduction
)";
            OleDbParameter[] paras = { new OleDbParameter("ID",artWork.ID),
                                     new OleDbParameter("ArtistID", artWork.ArtistID) ,
                                     new OleDbParameter("ArtistCategory", artWork.ArtistCategory),
                                     new OleDbParameter("ArtWorkName", artWork.ArtWorkName),
                                     new OleDbParameter("Spec", artWork.Spec),
                                     new OleDbParameter("PrePrice", artWork.PrePrice), 
                                     new OleDbParameter("SaleTime", artWork.SaleTime),
                                     new OleDbParameter("SaleCompany",artWork.SaleCompany),
                                     new OleDbParameter("SaleAuct",artWork.SaleAuct),
                                     new OleDbParameter("AuctState",artWork.AuctState),
                                     new OleDbParameter("TransactionPrice",artWork.TransactionPrice),
                                     new OleDbParameter("Introduction",artWork.Introduction)
                                   };
            dbhelper.ExecuteSQL_InTrans(sql, paras);


            //保存附件
            foreach (var item in artWork.AttachmentList)
            {
                sql = "insert into tbNewsAttachment(NewsID ,FileName ,SaveName) values('" + artWork.ID + "','" + item.FileName + "','" + item.SaveName + "')";
                dbhelper.ExecuteSQL(sql);
            }
        }

        public void DeleteArtWork(string id)
        {
            string sql = "delete from tbNewsAttachment where NewsId = '" + id + "'";
            dbhelper.ExecuteSQL(sql);

            sql = "delete from tbArtWork where ID = '" + id + "'";
            dbhelper.ExecuteSQL(sql);
        }

        public void UpdateArtWork(ArtWorkInfo artWork)
        {
            string sql = @"
update tbArtWork 
set ArtistID = @ArtistID , ArtistCategory = @ArtistCategory, ArtWorkName = @ArtWorkName ,Spec = @Spec ,
    PrePrice = @PrePrice ,SaleTime = @SaleTime,SaleCompany = @SaleCompany,SaleAuct = @SaleAuct,
    AuctState = @AuctState,TransactionPrice = @TransactionPrice,Introduction = @Introduction
where ID = '" + artWork.ID + "'";

            OleDbParameter[] paras = { new OleDbParameter("ArtistID", artWork.ArtistID) , 
                                       new OleDbParameter("ArtistCategory",artWork.ArtistCategory),
                                       new OleDbParameter("ArtWorkName", artWork.ArtWorkName) ,
                                       new OleDbParameter("Spec",artWork.Spec),
                                       new OleDbParameter("PrePrice",artWork.PrePrice),
                                       new OleDbParameter("SaleTime",artWork.SaleTime),
                                       new OleDbParameter("SaleCompany",artWork.SaleCompany),
                                       new OleDbParameter("SaleAuct",artWork.SaleAuct),
                                       new OleDbParameter("AuctState",artWork.AuctState),
                                       new OleDbParameter("TransactionPrice",artWork.TransactionPrice),
                                       new OleDbParameter("Introduction",artWork.Introduction),
                                   };

            dbhelper.ExecuteSQL(sql, paras);

            //更新附件列表
            sql = "delete from tbNewsAttachment where NewsId = '" + artWork.ID + "'";
            dbhelper.ExecuteSQL(sql);

            foreach (var item in artWork.AttachmentList)
            {
                sql = "insert into tbNewsAttachment(NewsID ,FileName ,SaveName) values('" + artWork.ID + "','" + item.FileName + "','" + item.SaveName + "')";
                dbhelper.ExecuteSQL(sql);
            }
        }

        public IList<AttachmentInfo> LoadArtWorkPic()
        {
            string sql = "select b.* from tbArtWork a left join tbNewsAttachment b on a.ID=b.NewsID";
            DataTable dtAtta = dbhelper.QueryForDataTable(sql);

            List<AttachmentInfo> AttachmentList = new List<AttachmentInfo>();

            foreach (DataRow drAtta in dtAtta.Rows)
            {
                AttachmentInfo attachment = new AttachmentInfo();
                attachment.NewsID = drAtta["NewsID"].ToString();
                attachment.FileName = drAtta["FileName"].ToString();
                attachment.SaveName = drAtta["SaveName"].ToString();
                AttachmentList.Add(attachment);
            }

            return AttachmentList;
        }

        public IList<AttachmentInfo> LoadTopWorkPic()
        {
            string sql = "select * from tbNewsAttachment where NewsID in(select top 6 ID from tbArtWork)";
            DataTable dtAtta = dbhelper.QueryForDataTable(sql);

            List<AttachmentInfo> AttachmentList = new List<AttachmentInfo>();

            foreach (DataRow drAtta in dtAtta.Rows)
            {
                AttachmentInfo attachment = new AttachmentInfo();
                attachment.NewsID = drAtta["NewsID"].ToString();
                attachment.FileName = drAtta["FileName"].ToString();
                attachment.SaveName = drAtta["SaveName"].ToString();
                AttachmentList.Add(attachment);
            }

            return AttachmentList;
        }

        /// <summary>
        /// 根据作家筛选作品
        /// </summary>
        /// <param name="artistname"></param>
        /// <returns></returns>
        public IList<AttachmentInfo> LoadArtWorkPic(string artistid)
        {
            string sql = "select b.* from tbArtWork a left join tbNewsAttachment b where a.ArtistID='" + artistid + "'";
            DataTable dtAtta = dbhelper.QueryForDataTable(sql);

            List<AttachmentInfo> AttachmentList = new List<AttachmentInfo>();

            foreach (DataRow drAtta in dtAtta.Rows)
            {
                AttachmentInfo attachment = new AttachmentInfo();
                attachment.NewsID = drAtta["NewsID"].ToString();
                attachment.FileName = drAtta["FileName"].ToString();
                attachment.SaveName = drAtta["SaveName"].ToString();
                AttachmentList.Add(attachment);
            }

            return AttachmentList;
        }


        public ArtWorkInfo LoadArtworkByID(string id)
        {
            string sql = "select tbArtWork.*,tbArtist.ArtistName from tbArtWork left join tbArtist on tbArtWork.ArtistID=tbArtist.ArtistID where id = '" + id + "'";

            DataTable dtArtist = dbhelper.QueryForDataTable(sql);

            DataRow dr = dtArtist.Rows[0];
            ArtWorkInfo artWork = new ArtWorkInfo();
            artWork.ID = dr["ID"].ToString();
            artWork.ArtistID = dr["ArtistID"].ToString();
            artWork.ArtistName = dr["ArtistName"].ToString();
            artWork.ArtistCategory = dr["ArtistCategory"].ToString();
            artWork.ArtWorkName = dr["ArtWorkName"].ToString();
            artWork.Spec = dr["Spec"].ToString();
            artWork.PrePrice = Convert.ToDouble(dr["PrePrice"].ToString());
            artWork.SaleTime = dr["SaleTime"].ToString();
            artWork.SaleCompany = dr["SaleCompany"].ToString();
            artWork.SaleAuct = dr["SaleAuct"].ToString();
            artWork.AuctState = dr["AuctState"].ToString();
            artWork.TransactionPrice = Convert.ToDouble(dr["TransactionPrice"]);
            artWork.Introduction = dr["Introduction"].ToString();


            DataTable dtAtta = dbhelper.QueryForDataTable("select * from tbNewsAttachment where NewsId = '" + id + "'");

            foreach (DataRow drAtta in dtAtta.Rows)
            {
                AttachmentInfo attachment = new AttachmentInfo();
                attachment.FileName = drAtta["FileName"].ToString();
                attachment.SaveName = drAtta["SaveName"].ToString();
                artWork.AttachmentList.Add(attachment);
            } 

            return artWork;
        }

        public IList<ArtWorkInfo> LoadAllArtwork()
        {
            IList<ArtWorkInfo> artworks = new List<ArtWorkInfo>();
            string sql = "select * from tbArtWork"; //todo 考虑分页
            DataTable dtArtist = dbhelper.QueryForDataTable(sql);
            foreach (DataRow dr in dtArtist.Rows)
            {
                ArtWorkInfo artWork = new ArtWorkInfo();
                artWork.ID = dr["ID"].ToString();
                artWork.ArtistID = dr["ArtistID"].ToString();
                artWork.ArtistCategory = dr["ArtistCategory"].ToString();
                artWork.ArtWorkName = dr["ArtWorkName"].ToString();
              
                artworks.Add(artWork);
            }
            return artworks;
        }

        public IList<ArtWorkInfo> LoadTopArtWork()
        {
            IList<ArtWorkInfo> artworks = new List<ArtWorkInfo>();
            string sql = "select top 6 * from tbArtWork order by SaleTime Desc"; //todo 考虑分页
            DataTable dtArtist = dbhelper.QueryForDataTable(sql);
            foreach (DataRow dr in dtArtist.Rows)
            {
                ArtWorkInfo artWork = new ArtWorkInfo();
                artWork.ID = dr["ID"].ToString();
                artWork.ArtistID = dr["ArtistID"].ToString();
                artWork.ArtistCategory = dr["ArtistCategory"].ToString();
                artWork.ArtWorkName = dr["ArtWorkName"].ToString();

                artworks.Add(artWork);
            }
            return artworks;
        }
    }
}
